﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChBusiness.Struct;
using WF_Business;

namespace ChBusiness.Wsbs
{
    /// <summary>
    /// 办件流程实例操作类
    /// </summary>
    public class SerialInstance
    {
        /// <summary>
        /// 实例化对象
        /// </summary>
        public static SerialInstance Instance { get { return new SerialInstance(); } }

        Database dbTemp = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// 获取正在办理事项
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetZzblInstance(string strUserId, string strIID, string strName)
        {
            //添加业务删除标识条件 and t.isdelete <>1 
            //包括预审通过的业务 or submitflag = '3' 
            string strSql = string.Format(@"select IID,
                                       FLOWNAME,
                                       flowtype,
                                       flowid,
                                       REQUESTER,
                                       SUBMITFLAG,
 (select t.图名
          from ut_国家测绘局地图审核申请表 t
         where t.iid = a.iid
           and t.input_index = 0 and rownum<2) tm,
       (select slbh
          from ut_地图审核受理通知书 t
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') INPUTDATE,
                                       remark,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') INPUTDATE,
                                       to_char(INPUTDATE, 'yyyy-mm-dd') edittime,
(select iid from xt_instance_ext b where b.关联id=a.iid and rownum<2) gliid,IS_BG,IS_YQ,IS_CX,
                                       to_char(edittime, 'yyyy-mm-dd') FINISH_TIME
                                  from xt_submit_info a
                                 where (submitflag>=1 or submitflag=-4) 
 and not exists (select iid from st_instance m where m.iid=a.iid and m.status=2)
                                   and isdelete <> '1'
                                   and requester = '{0}'", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and REQUESTER like '%{0}%'", strName);
            }
            strSql += " order by iid desc";
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取正在办理事项个数
        /// </summary>
        /// <returns></returns>
        public string GetInstanceCount(string strUserId)
        {
            //添加业务删除标识条件 and t.isdelete <>1  addby zhongjian 20091223
            string strSql = string.Empty;
            strSql = string.Format(@"SELECT COUNT(*)  from xt_submit_info a WHERE (submitflag>=1 or submitflag=-4) and REQUESTER = '{0}'  and not exists (select iid from st_instance m where m.iid=a.iid and m.status=2)", strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取历史办件信息
        /// </summary>
        /// <param name="strUserId">申请人名称</param>
        /// <param name="strStatus">业务状态(0:未提交;1:已提交;2:已通过;3:预审;4:协审通过;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过)</param>
        /// <param name="strIID">办件编号</param>
        /// <returns></returns>
        public DataTable GetCompleteInstance(string strUserId, string strStatus, string strIID)
        {
            //添加业务删除标识条件
            string strSql = string.Format(@"select a.IID,d.flowtype,d.flowname,NAME AS REQUESTER,
       (select t.图名
          from ut_国家测绘局地图审核申请表 t
         where t.iid = a.iid
           and t.input_index = 0 and rownum<2) tm,
       (select decode(d.submitflag,2,t.批准号,-4,t.不予批准号,4,t.协审批准号)  
          from ut_国家测绘局地图审核申请表 t
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
       to_char(a.createtime, 'yyyy-mm-dd') INPUTDATE,
       to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
       '审核完成' remark,
       d.submitflag,
       to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
  from st_instance a, xt_submit_info d
 where a.status = 2
   and a.iid = d.iid
   and d.REQUESTER = '{0}'
   and a.iid in (select b.iid
                   from xt_instance_ext b
                  where b.status in ({1}))", strUserId, strStatus);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and a.IID like '%{0}%'", strIID);
            }
            strSql += " order by ACCEPTED_TIME desc";
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取不予受理案件信息
        /// </summary>
        /// <param name="strUserId">申请人名称</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strStatus">申请业务状态（0:未提交;2:已办结;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过）</param>
        /// <returns></returns>
        public DataTable GetNoPass(string strUserId, string strIID, string strStatus)
        {
            string strSql = string.Format(@"select d.IID,d.flowtype,d.flowname,NAME AS REQUESTER,
       (select t.图名
          from ut_国家测绘局地图审核申请表 t
         where t.iid = a.iid
           and t.input_index = 0 and rownum<2) tm,
       (select slbh 
          from ut_地图审核受理通知书 t
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
       to_char(a.createtime, 'yyyy-mm-dd') INPUTDATE,
       to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
       d.remark,
       d.submitflag,
       to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
  from st_instance a, xt_instance_ext c, xt_submit_info d
 where a.iid = c.iid
   and c.关联ID = d.iid
   and d.REQUESTER = '{0}'", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and d.IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strStatus))
            {
                strSql += string.Format(" and c.status={0}", strStatus);
            }
            strSql += " order by ACCEPTED_TIME desc";

            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 按业务流程状态获取办理事项个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <param name="strStatus">提交状态(0:未提交;2:已办结;-1:返回补证;-2:驳回中止;-3:不予受理;-4:审批不通过)</param>
        /// <returns></returns>
        /// <!--//添加业务删除标识条件 and a.isdelete <>1  addby zhongjian 20091223-->
        /// <!--//去掉or status = -2条件 不包括被驳回的事项 update by zhongjian 20100316-->
        public string GetHistoryCount(string strUserId, string strStatus)
        {
            string strSql = string.Format(@"select count(*)
                                              from st_instance a
                                             where a.isdelete <> 1
                                               and a.iid in (select b.iid
                                                               from xt_instance_ext b, xt_submit_info c
                                                              where b.关联ID = c.iid
                                                                and b.status = '{1}'
                                                                and c.REQUESTER = '{0}')", strUserId, strStatus);

            if (strStatus != "-3")
            {
                strSql += " and status='2'";
            }

            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取驳回办理事项
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <param name="strIID">办件编号</param>
        /// <param name="strName">申请人</param>
        /// <returns></returns>
        public DataTable GetBoHuiHistory(string strUserId, string strIID, string strName)
        {
            //添加业务删除标识条件 and a.isdelete <>1 
            string strSql = string.Format(@"select a.IID,
                   b.stop_remark remark,
                   d.flowtype,
                   d.FLOWNAME,
                   a.WID,
                   c.sync_type,
(select t.图名
          from ut_国家测绘局地图审核申请表 t
         where t.iid = a.iid
           and t.input_index = 0 and rownum<2) tm,
       (select slbh 
          from ut_地图审核受理通知书 t
         where t.iid = a.iid
           and input_index = 0
           and rownum < 2) slh,
                   NAME AS REQUESTER,c.submitflag,
                   to_char(c.inputdate, 'yyyy-mm-dd') AS INPUTDATE,
                   to_char(ACCEPTED_TIME, 'yyyy-mm-dd') edittime,
                   to_char(FINISH_TIME, 'yyyy-mm-dd') FINISH_TIME
              from st_instance        a,
                   xt_instance_ext    b,
                   xt_submit_info     c,
                   xt_workflow_define d
             where a.status = -2
               and a.isdelete <>1 
               and a.iid = b.iid
               and b.关联id = c.iid
               and a.iid in (select b.iid
                               from xt_instance_ext b, xt_submit_info c
                              where b.关联ID = c.iid
                                and c.REQUESTER = '{0}')
               and c.flowname = d.flowname
               and d.ispub = 1
               and d.isdelete = 0", strUserId);

            if (!string.IsNullOrEmpty(strIID))
            {
                strSql += string.Format(" and a.IID like '%{0}%'", strIID);
            }
            if (!string.IsNullOrEmpty(strName))
            {
                strSql += string.Format(" and NAME like '%{0}%'", strName);
            }
            strSql += "  order by ACCEPTED_TIME desc";
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取驳回办理事项个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <returns></returns>
        public string GetBoHuiCount(string strUserId)
        {
            //添加业务删除标识条件 and a.isdelete <>1
            string strSql = string.Format(@"select count(*) from st_instance a where status=-2 and a.isdelete <>1
                and a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}')",
                strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取累计收件个数
        /// </summary>
        /// <param name="strUserId">申报人名称</param>
        /// <returns></returns>
        /// <!--添加删除标识条件 删除之后的案件不在统计当中-->
        public string GetShoujianCount(string strUserId)
        {
            string strSql = string.Format(@"select count(*) from xt_submit_info c where c.REQUESTER='{0}' and isdelete <>'1'", strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取所有累计收件个数
        /// </summary>
        /// <returns></returns>
        public string GetShoujianTotal()
        {
            //添加业务删除标识条件 and isdelete <>1  addby zhongjian 20091223
            string strSql = @"select count(iid) from xt_submit_info c where isdelete <>1";
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取累计办结个数
        /// </summary>
        /// <returns></returns>
        public string GetBanjieCount(string strUserId)
        {
            //添加业务删除标识条件 and a.isdelete <>1
            string strSql = string.Format(@"select count(*) from st_instance a where 
                (STATUS=2 or STATUS=-2) and a.isdelete <>1 and a.iid in (select b.iid from 
                 xt_instance_ext b,xt_submit_info c where b.关联ID=c.iid and c.REQUESTER='{0}')",
                 strUserId);
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 获取所有累计办结个数
        /// </summary>
        /// <returns></returns>
        public string GetBanjieTotal()
        {
            //添加业务删除标识条件 and a.isdelete <>1
            string strSql = @"select count(*)
                  from st_instance a
                 where (STATUS = 2 or STATUS = -2) and a.isdelete <>1";
            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            object result = dbTemp.ExecuteScalar(dbCmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 按业务编号获取正在办理业务详细信息
        /// </summary>
        /// <param name="strSerial">业务编号</param>
        /// <returns></returns>
        public DataTable GetSerialList(string strSerial)
        {
            //添加业务删除标识条件 and b.isdelete <>1
            string strSql = string.Format(@"select 编号 SERIAL,
                   岗位编号 办理环节编号,
                   经办人   办理人员,
                   岗位     办理处室,
                   接件时间 办理时间,
                   办理操作,
                   isback   是否回退,
                   b.status
              FROM sv_work_item_list a, st_instance b
             where a.编号 = b.iid and b.iid='{0}'
             order by 办理时间 asc", strSerial);
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        /// <summary>
        /// 获取用户密码
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public string GetUserPWD(string strUserID)
        {
            string strSql = string.Format(@"select userpwd from sys_user where userid='{0}'", strUserID);

            DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
            return dbTemp.ExecuteScalar(dbCmd).ToString();
        }

        /// <summary>
        /// 获取当前用户消息提示步骤名称
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public string GetStepName(string strUserID)
        {
            string strSql = string.Format(@"select step_name from xt_request_step where step_no=
                            (select stepno from sys_user where userid='{0}')", strUserID);
            try
            {
                DbCommand dbCmd = dbTemp.GetSqlStringCommand(strSql);
                return dbTemp.ExecuteScalar(dbCmd).ToString();
            }
            catch
            {
                return "";
            }
        }


        /// <summary>
        /// 获取用户有权限办理的事项
        /// </summary>
        /// <param name="strUserID">用户ID</param>
        /// <returns></returns>
        public DataTable GetNewWork(string strUserID)
        {
            string strSql = string.Format(@"select a.id, b.flowname, b.flowtype, b.interfaceurl, b.interfacetype
                                              from SYS_USER_SERIAL a, xt_workflow_define b
                                             where is_pass = 1
                                               and a.serial_name = b.flowname
                                               and b.isdelete = '0'
                                               and b.ispub = '1'
                                               and UserID = '{0}'
                                             order by serial_time desc", strUserID);
            return dbTemp.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }


        /// <summary>
        /// 通过申报类型获取申报信息
        /// </summary>
        /// <param name="type">申报类型(0:尚未提交事项;1:当前办理事项;2:补正补齐办理事项;3:通过办理事项;4.驳回办理事项;5.不予受理事项;6.未通过办理事项;)</param>
        /// <returns></returns>
        public string GetAllShenBaoInfo(string type, string iid, string userId, string flowname, string unitsid, int startpage, int endpage)
        {
            DataTable tmpDt = new DataTable();
            string strResult = string.Empty;
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(type))
            {
                strSql = "select rownum idx,t.* from INTERFACE_all t where 1=1";
            }
            else
            {
                switch (type)
                {
                    case "0":
                        strSql = "select rownum idx,t.* from INTERFACE_WTJ t where 1=1";
                        break;
                    case "1":
                        strSql = "select rownum idx,t.* from INTERFACE_DQBL t where 1=1";
                        break;
                    case "2":
                        strSql = "select rownum idx,t.* from INTERFACE_BZBQBL t where 1=1";
                        break;
                    case "3":
                        strSql = "select rownum idx,slbh,tm,IID,flowname,INPUTDATE,edittime,'审核通过' submitflag,requester,FINISH_TIME,sth,unitsid，notes from INTERFACE_TGBLWITHBTG t where t.submitflag=3";
                        break;
                    //case "4":
                    //    strSql = "select * from INTERFACE_BHBL t where 1=1";
                    //    break;
                    case "5":
                        strSql = "select rownum idx,t.* from INTERFACE_BYSL t where 1=1";
                        break;
                    case "6":
                        strSql = "select rownum idx,slbh,tm,IID,flowname,INPUTDATE,edittime,'审核未通过' submitflag,requester,FINISH_TIME,sth,unitsid，notes from INTERFACE_TGBLWITHBTG t where t.submitflag=6";
                        break;
                }
            }
            if (!string.IsNullOrEmpty(strSql))
            {
                if (!string.IsNullOrEmpty(iid) && iid.ToLower() != "null")
                {
                    strSql += string.Format(" and iid='{0}'", iid);
                }
                if (!string.IsNullOrEmpty(userId))
                {
                    strSql += string.Format(" and requester='{0}'", userId);
                }
                if (!string.IsNullOrEmpty(flowname))
                {
                    strSql += string.Format("and flowname='{0}'", flowname);
                }
                if (!string.IsNullOrEmpty(unitsid))
                {
                    strSql += string.Format(" and unitsid='{0}'", unitsid);
                }
                string strCmd = string.Format("select count (*) as zs from ({0})", strSql);
                DataTable dtCount = dbTemp.ExecuteDataSet(CommandType.Text, strCmd).Tables[0];
                if (dtCount.Rows.Count > 0)
                {
                    strResult += string.Format("<recordinfo count='{0}'>", dtCount.Rows[0]["zs"]);
                }
                else
                {
                    strResult += "<recordinfo count='0'>";
                }

                //重新封装
                string strExecSql = string.Format("select * from ({0}) m where 1=1", strSql);
                if (startpage > 0)
                {
                    strExecSql += string.Format(" and idx>={0}", startpage);
                }
                if (endpage > startpage)
                {
                    strExecSql += string.Format(" and idx<={0}", endpage);
                }

                tmpDt = dbTemp.ExecuteDataSet(CommandType.Text, strExecSql).Tables[0];
                if (tmpDt.Rows.Count > 0)
                {
                    for (int i = 0; i < tmpDt.Rows.Count; i++)
                    {
                        strResult += string.Format("<trans><id>{0}</id>", tmpDt.Rows[i]["IID"]);
                        strResult += string.Format("<sxmc>{0}</sxmc>", tmpDt.Rows[i]["FLOWNAME"]);
                        strResult += string.Format("<cjsj>{0}</cjsj>", tmpDt.Rows[i]["INPUTDATE"]);
                        strResult += string.Format("<xgsj>{0}</xgsj>", tmpDt.Rows[i]["EDITTIME"]);
                        strResult += string.Format("<unitsid>{0}</unitsid>", tmpDt.Rows[i]["unitsid"]);
                        strResult += string.Format("<zt>{0}</zt>", tmpDt.Rows[i]["submitflag"]);
                        strResult += string.Format("<slbh>{0}</slbh>", tmpDt.Rows[i]["slbh"]);
                        strResult += string.Format("<tm>{0}</tm>", tmpDt.Rows[i]["tm"]);

                        if (tmpDt.Columns.Contains("sth"))
                        {
                            strResult += string.Format("<sth>{0}</sth>", tmpDt.Rows[i]["sth"]);
                        }
                        strResult += string.Format("<notes>{0}</notes></trans>", tmpDt.Rows[i]["notes"]);
                    }
                }
                strResult += "</recordinfo>";
                //  strResult += "</operationXml>";
            }
            return strResult;
        }

        /// <summary>
        /// 通过申报类型获取申报信息
        /// </summary>
        /// <param name="type">申报类型(0:尚未提交事项;1,3:当前办理事项;-1:补正补齐办理事项;2:通过办理事项;-3.不予受理事项;-4.未通过办理事项;)</param>
        /// <returns></returns>
        public string GetAllShenBaoInfoNew(string type, string iid, string userId, string flowname, string unitsid, int startpage, int endpage)
        {
            DataTable tmpDt = new DataTable();
            string strResult = string.Empty;
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(type))
            {
                strSql = "select rownum idx,t.* from INTERFACE_all t where 1=1";
            }
            else
            {
                strSql += string.Format("select rownum idx,t.* from INTERFACE_all t where type in ({0})", type);
            }
            if (!string.IsNullOrEmpty(unitsid))
            {
                strSql += string.Format(" and unitsid='{0}'", unitsid);
            }
            if (!string.IsNullOrEmpty(iid) && iid.ToLower() != "null")
            {
                strSql += string.Format(" and iid='{0}'", iid);
            }
            if (!string.IsNullOrEmpty(userId))
            {
                strSql += string.Format(" and requester='{0}'", userId);
            }
            if (!string.IsNullOrEmpty(flowname))
            {
                strSql += string.Format("and flowname='{0}'", flowname);
            }

            string strExecSql = string.Format("select count (*) as zs from ({0})", strSql);
            string strCount = SysParams.OAConnection().GetValue(strExecSql);
            if (!string.IsNullOrEmpty(strCount))
            {
                strResult += string.Format("<recordinfo count='{0}'>", strCount);
            }
            else
            {
                strResult += "<recordinfo count='0'>";
            }

            //重新封装
            strExecSql = string.Format("select * from ({0}) m where 1=1", strSql);
            if (startpage > 0)
            {
                strExecSql += string.Format(" and idx>={0}", startpage);
            }
            if (endpage > startpage)
            {
                strExecSql += string.Format(" and idx<={0}", endpage);
            }

            tmpDt = dbTemp.ExecuteDataSet(CommandType.Text, strExecSql).Tables[0];

            for (int i = 0; i < tmpDt.Rows.Count; i++)
            {
                strResult += string.Format("<trans><id>{0}</id>", tmpDt.Rows[i]["IID"]);
                strResult += string.Format("<sxmc>{0}</sxmc>", tmpDt.Rows[i]["FLOWNAME"]);
                strResult += string.Format("<cjsj>{0}</cjsj>", tmpDt.Rows[i]["INPUTDATE"]);
                strResult += string.Format("<zt>{0}</zt>", tmpDt.Rows[i]["submitflag"]);
                strResult += string.Format("<unitsid>{0}</unitsid>", tmpDt.Rows[i]["unitsid"]);
                strResult += string.Format("<notes>{0}</notes></trans>", tmpDt.Rows[i]["notes"]);
            }

            strResult += "</recordinfo>";

            return strResult;
        }

        
        /// <summary>
        /// 获取地图审核申报信息
        /// </summary>
        /// <param name="type">申报类型(0:尚未提交事项;1,3:当前办理事项;-1:补正补齐办理事项;2:通过办理事项;-3.不予受理事项;-4.未通过办理事项;)</param>
        /// <param name="iid"></param>
        /// <param name="userId"></param>
        /// <param name="tm">图名</param>
        /// <param name="sth"></param>
        /// <param name="slbh"></param>
        /// <param name="yt"></param>
        /// <param name="startpage"></param>
        /// <param name="endpage"></param>
        /// <returns></returns>
        public string GetDtshInfo(string type, string iid, string userId, string unitsid,string tm,string sth,string slbh,string yt, int startpage, int endpage)
        {
            DataTable tmpDt = new DataTable();
            string strResult = string.Empty;
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(type))
            {
                strSql = "select rownum idx,t.* from interface_dtsh_all t where 1=1";
            }
            else
            {
                strSql += string.Format("select rownum idx,t.* from interface_dtsh_all t where type in ({0})", type);
            }
            if (!string.IsNullOrEmpty(unitsid))
            {
                strSql += string.Format(" and unitsid='{0}'", unitsid);
            }
            if (!string.IsNullOrEmpty(iid) && iid.ToLower() != "null")
            {
                strSql += string.Format(" and iid='{0}'", iid);
            }
            if (!string.IsNullOrEmpty(userId))
            {
                strSql += string.Format(" and requester='{0}'", userId);
            }
            if (!string.IsNullOrEmpty(tm))
            {
                strSql += string.Format(" and tm like '%{0}%'", tm);
            }
            if (!string.IsNullOrEmpty(sth))
            {
                strSql += string.Format(" and sth like '%{0}%'", sth);
            }
            if (!string.IsNullOrEmpty(slbh))
            {
                strSql += string.Format(" and slbh like '%{0}%'", slbh);
            }
            if (!string.IsNullOrEmpty(yt))
            {
                strSql += string.Format(" and yt like '%{0}%'", yt);
            }

            string strExecSql = string.Format("select count (*) as zs from ({0})", strSql);
            string strCount = SysParams.OAConnection().GetValue(strExecSql);
            if (!string.IsNullOrEmpty(strCount))
            {
                strResult += string.Format("<recordinfo count='{0}'>", strCount);
            }
            else
            {
                strResult += "<recordinfo count='0'>";
            }

            //重新封装
            strExecSql = string.Format("select * from ({0}) m where 1=1", strSql);
            if (startpage > 0)
            {
                strExecSql += string.Format(" and idx>={0}", startpage);
            }
            if (endpage > startpage)
            {
                strExecSql += string.Format(" and idx<={0}", endpage);
            }

            tmpDt = dbTemp.ExecuteDataSet(CommandType.Text, strExecSql).Tables[0];

            for (int i = 0; i < tmpDt.Rows.Count; i++)
            {
                strResult += string.Format("<trans><id>{0}</id>", tmpDt.Rows[i]["IID"]);
                strResult += string.Format("<sxmc>{0}</sxmc>", tmpDt.Rows[i]["FLOWNAME"]);
                strResult += string.Format("<cjsj>{0}</cjsj>", tmpDt.Rows[i]["INPUTDATE"]);
                strResult += string.Format("<zt>{0}</zt>", tmpDt.Rows[i]["submitflag"]);
                strResult += string.Format("<slbh>{0}</slbh>", tmpDt.Rows[i]["slbh"]);
                strResult += string.Format("<tm>{0}</tm>", tmpDt.Rows[i]["tm"]);
                strResult += string.Format("<yt>{0}</yt>", tmpDt.Rows[i]["yt"]);
                strResult += string.Format("<sth>{0}</sth>", tmpDt.Rows[i]["sth"]);
                strResult += string.Format("<unitsid>{0}</unitsid>", tmpDt.Rows[i]["unitsid"]);
                strResult += string.Format("<notes>{0}</notes></trans>", tmpDt.Rows[i]["notes"]);
            }

            strResult += "</recordinfo>";

            return strResult;
        }

        /// <summary>
        /// 根据订单号获取审批结果
        /// </summary>
        /// <param name="appid"></param>
        /// <returns></returns>
        public string GetApprovalResult(string appid)
        {
            var strSql = string.Format(@"select a.appid,
                                   c.使用目的,
                                   c.项目名称,
                                   c.批准单位名称,
                                   nvl2(c.单位性质1, '国家机关、', '') || nvl2(c.单位性质2, '事业、', '') ||
                                   nvl2(c.单位性质3, '企业、', '') || nvl2(c.单位性质4, '军队、', '') ||
                                   nvl2(c.单位性质5, '其他、', '') 单位性质,
                                   c.批准文号,
                                   case g.submitflag 
                                   when 0 then '未提交'
                                   when 1 then '待预审'
                                   when 3 then '预审通过'
                                   when -1 then '补正补齐'
                                   when -3 then '不予受理'
                                   when 2 then '初审通过'
                                   when -4 then '已初审' end 办理结果
                              from xt_submit_info g, ut_测绘成果使用申请表 c, xt_appid_iid a
                             where g.iid = c.iid
                               and g.iid = a.iid
                               and a.appid = '{0}'", appid);
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            var builder = new StringBuilder();
            builder.AppendFormat("<recordinfo count='{0}'>", dtTemp.Rows.Count);
            dtTemp.Rows.Cast<DataRow>().ToList().ForEach(row =>
            {
                builder.AppendFormat("<trans><approval_id>{0}</approval_id>", row["appid"].ToString());
                builder.AppendFormat("<purpose>{0}</purpose>", row["使用目的"].ToString());
                builder.AppendFormat("<project_name>{0}</project_name>", row["项目名称"].ToString());
                builder.AppendFormat("<dept>{0}</dept>", row["批准单位名称"].ToString());
                builder.AppendFormat("<dept_type>{0}</dept_type>", row["单位性质"].ToString().TrimEnd('、'));
                builder.AppendFormat("<approval_number>{0}</approval_number>", row["批准文号"].ToString());
                builder.AppendFormat("<approval_state>{0}</approval_state></trans>", row["办理结果"].ToString());
            });
            builder.Append("</recordinfo>");
            return builder.ToString();
        }

        /// <summary>
        /// 更新涉密成果信息
        /// </summary>
        /// <param name="rsName"></param>
        /// <param name="applyType"></param>
        /// <param name="rsRange"></param>
        /// <param name="rsJd"></param>
        /// <param name="rsCount"></param>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string UpdateRsInfo(string rsName, string applyType, string rsRange, string rsJd, string rsCount, string iid)
        {
            var rsType = string.Concat(applyType, "、", rsRange, "、", rsJd, "、", rsCount);
            var strSql = string.Format("update UT_测绘成果使用申请表  set 成果资料名称='{0}',种类='{1}' where iid='{2}'", rsName, rsType, iid);
            return SysParams.OAConnection().RunSql(strSql) > 0 ? "true" : "false";
        }
    }
}
